﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Pantry.Commons
{
    public static class DBSchemas
    {
        #region HAINHH1
        #region Contact
        public const string CheckUserIsExists = @"SELECT COUNT(ContactID) FROM Contact WHERE MobilePhone = @MobilePhone AND Email = @Email";
        public const string GetManagerName = @"SELECT (FirstName + ' ' + SurName) AS FullName FROM Contact WHERE ContactID = @ContactId";
        public const string CreateNewContact = @"
                                                INSERT INTO Contact
                                                (
                                                    FirstName,	
	                                                SurName, 
	                                                KnownAs,
	                                                OfficePhone,
	                                                MobilePhone,
	                                                HomePhone,
	                                                Email,
	                                                ManagerID,
	                                                ContactTypeId,
	                                                BestContactMethodId,
	                                                JobRole,
	                                                Workbase,
	                                                JobTitle,
	                                                IsActive
                                                )
                                                VALUES
                                                (
                                                    @FirstName,
	                                                @SurName,
	                                                @KnownAs,
	                                                @OfficePhone,
	                                                @MobilePhone,
	                                                @HomePhone,
	                                                @Email,
	                                                @ManagerId,
	                                                @ContactTypeId,
	                                                @BestContactMethodId,
	                                                @JobRole,
	                                                @Workbase,
	                                                @JobTitle,
	                                                @IsActive
                                                )
                                                ";
        public const string ListAllContact = @"
                                                SELECT  ContactID, FirstName, SurName, (FirstName + ' ' + SurName) AS FullName, 
                                                        KnownAs, OfficePhone, MobilePhone, HomePhone, Email, ManagerID, 
                                                        ContactType, BestContactMethod, JobRole, Workbase, JobTitle, IsActive
		                                        FROM    Contact 
		                                        INNER JOIN ContactType  ON Contact.ContactTypeId = ContactType.ID
		                                        INNER JOIN ContactMethod ON Contact.BestContactMethodId = ContactMethod.ID
                                                ";
        public const string ListActiveContact = @"
                                                SELECT  ContactID, FirstName, SurName, (FirstName + ' ' + SurName) AS FullName, 
                                                        KnownAs, OfficePhone, MobilePhone, HomePhone, Email, ManagerID, 
                                                        ContactTypeId, ContactType, BestContactMethodId, BestContactMethod, JobRole, Workbase, JobTitle, IsActive
		                                        FROM    Contact 
		                                        INNER JOIN ContactType ON Contact.ContactTypeId = ContactType.ID
		                                        INNER JOIN ContactMethod ON Contact.BestContactMethodId = ContactMethod.ID
		                                        WHERE IsActive = 1
                                                ";
        public const string UpdateContact = @"
                                                UPDATE Contact		
                                                SET                                                		
	                                                FirstName = @FirstName,
	                                                SurName = @SurName,
	                                                KnownAs = @KnownAs,
	                                                OfficePhone = @OfficePhone,
	                                                MobilePhone = @MobilePhone,
	                                                HomePhone = @HomePhone,
	                                                Email = @Email,
	                                                ManagerId = @ManagerId,
	                                                ContactTypeId = @ContactTypeId,
	                                                BestContactMethodId = @BestContactMethodId,
	                                                JobRole = @JobRole,
	                                                Workbase = @Workbase,
	                                                JobTitle = @JobTitle,
	                                                IsActive = @IsActive                                                		
                                                WHERE ContactID = @ContactId
                                                ";
        public const string GetContactById = @"SELECT * FROM Contact WHERE ContactID = @ContactId";
        #endregion

        #region ContactType

        public const string ListContactType = @"SELECT ID, ContactType From ContactType";
        public const string ListBestContactMethods = @"SELECT ID, BestContactMethod From ContactMethod";
        public const string GetContactTypeId = @"SELECT TOP 1 ID FROM ContactType WHERE ContactType = @ContactType";
        public const string GetContactMethodId = @"SELECT TOP 1 ID FROM ContactMethod WHERE BestContactMethod = @ContactMethod";

        #endregion

        #region Directorate

        public const string ListDirectorates = @"
                                                    SELECT 
	                                                    D.DirectorateId, D.OrganizationId, D.ContactId, (I.FirstName + ' ' + I.SurName) AS ContactName, 
                                                        D.DirectorateName, D.DirectorateShortDescription AS ShortDescription, 
	                                                    D.AddressLine1, D.AddressLine2, D.AddressLine3, D.Postcode, D.Town, 
                                                        D.County, D.Country, D.TypeOfBusinessId, D.SiCcode,
	                                                    D.DirectorateFullDescription AS FullDescription, D.PhoneNumber, 
                                                        D.Fax, D.Email, D.WebAddress, D.CharityNumber,D.CompanyNumber, CASE(D.[Status]) WHEN 1 THEN 'Yes' ELSE 'No' END As Status
                                                    FROM Directorate AS D
                                                    INNER JOIN Contact AS I ON D.ContactId = I.ContactId WHERE d.OrganizationId = @OrganizationId
                                                ";
        public const string CreateDirectorate = @"
                                                    INSERT INTO Directorate
                                                    (
	                                                    OrganizationId, ContactId, DirectorateName, DirectorateShortDescription, AddressLine1,
	                                                    AddressLine2, AddressLine3, Postcode, Town, County, Country, TypeOfBusinessId, 
	                                                    SiCcode, DirectorateFullDescription, PhoneNumber, Fax, Email, WebAddress, CharityNumber,
	                                                    CompanyNumber, [Status]
                                                    )
                                                    VALUES
                                                    (
	                                                    @OrganizationId, @ContactId, @DirectorateName, @ShortDescription, @AddressLine1,
	                                                    @AddressLine2, @AddressLine3, @Postcode, @Town, @County, @Country, @TypeOfBusinessId,
	                                                    @SiCcode, @FullDescription, @PhoneNumber, @Fax, @Email, @WebAddress, @CharityNumber, 
	                                                    @CompanyNumber, @Status
                                                    )
                                                ";
        public const string AmendDirectorate = @"
                                                    UPDATE Directorate
                                                    SET
                                                        OrganizationId = @OrganizationId,
                                                        ContactId = @ContactId,
                                                        DirectorateName = @DirectorateName,
                                                        DirectorateShortDescription = @ShortDescription,
                                                        AddressLine1 = @AddressLine1,
                                                        AddressLine2 = @AddressLine2,
                                                        AddressLine3 = @AddressLine3,
                                                        Postcode = @Postcode,
                                                        Town = @Town,
                                                        County = @County,
                                                        Country = @Country,
                                                        TypeOfBusinessId = @TypeOfBusinessId,
                                                        SICCode = @SiCcode,
                                                        DirectorateFullDescription = @FullDescription,
                                                        PhoneNumber = @PhoneNumber,
                                                        Fax = @Fax,
                                                        Email = @Email,
                                                        WebAddress = @WebAddress,
                                                        CharityNumber = @CharityNumber,
                                                        CompanyNumber = @CompanyNumber,
                                                        [Status] = @Status
                                                    WHERE DirectorateId = @DirectorateId
                                                ";
        public const string GetDirectorateById = @"
                                                    SELECT 
	                                                    D.DirectorateId, D.OrganizationId, D.ContactId, (I.FirstName + ' ' + I.SurName) AS ContactName, 
                                                        D.DirectorateName, D.DirectorateShortDescription AS ShortDescription, 
	                                                    D.AddressLine1, D.AddressLine2, D.AddressLine3, D.Postcode, D.Town, 
                                                        D.County, D.Country, D.TypeOfBusinessId, D.SiCcode,
	                                                    D.DirectorateFullDescription AS FullDescription, D.PhoneNumber, 
                                                        D.Fax, D.Email, D.WebAddress, D.CharityNumber,D.CompanyNumber, D.[Status]
                                                    FROM Directorate AS D
                                                    INNER JOIN Contact AS I ON D.ContactId = I.ContactId
                                                    WHERE D.DirectorateId = @DirectorateId
                                                    ";

        public const string ActivateDirectorate = @"
                                                    UPDATE  Directorate
                                                    SET     [Status] = 'False'
                                                    WHERE   DirectorateId = @DirectorateId
                                                    ";
        public const string MakeDirectorateIsActive = @"
                                                    UPDATE  Directorate
                                                    SET     [Status] = 'True'
                                                    WHERE   DirectorateId = @DirectorateId
                                                    ";
        public const string CheckDirectorateNameIsExist = @"
                                                    SELECT COUNT(DirectorateName) 
                                                    FROM Directorate 
                                                    WHERE DirectorateName = @DirectorateName
                                                    ";

        #endregion

        #region Organization Role

        public const string ListRole = @"
                                SELECT RoleId, RoleName
                                FROM [Role]
                                ";

        public const string RoleNameById = @"
                                SELECT RoleName
                                FROM [Role]
                                WHERE RoleId = @RoleId
                                ";
        public const string AddRole = @"
                                IF NOT EXISTS (SELECT * FROM Role WHERE RoleName = @RoleName)
                                BEGIN
	                                INSERT INTO Role VALUES(@RoleName)
                                END	
                                ";
        public const string ListRoleById = @"
                                SELECT OrganizationId, RoleId
                                FROM OrganizationRole
                                WHERE OrganizationId = @OrganizationId
                                ";
        #endregion

        #region Organization (HAINHH1)

        public const string ListOrganizationRole = @"
                                IF (EXISTS(
                                    SELECT *
                                    FROM INFORMATION_SCHEMA.TABLES
                                    WHERE TABLE_SCHEMA = 'dbo'
                                    AND TABLE_NAME = 'tmpOrganizationRole'
                                ))
                                    BEGIN
                                        DROP TABLE tmpOrganizationRole		
                                        SELECT  A.ID AS OrganizationId, A.Name AS OrganizationName, A.ServiceId,  
                                                CAST(A.ShortDescription AS VARCHAR(50)) AS ShortDescription, 
                                                CASE WHEN C.RoleName IS NULL THEN ' ' ELSE C.RoleName END AS RoleName INTO tmpOrganizationRole
                                        FROM Organizations A
                                        LEFT OUTER JOIN OrganizationRole B ON A.ID = B.OrganizationId
                                        LEFT OUTER JOIN Role C ON B.RoleId = C.RoleId
                                        ORDER BY OrganizationId
                                    END
                                ELSE 
                                    BEGIN 
                                        SELECT  A.ID AS OrganizationId, A.Name AS OrganizationName, A.ServiceId, 
                                                CAST(A.ShortDescription AS VARCHAR(50)) AS ShortDescription , 
                                                CASE WHEN C.RoleName IS NULL THEN ' ' ELSE C.RoleName END AS RoleName INTO tmpOrganizationRole
                                        FROM Organizations A
                                        LEFT OUTER JOIN OrganizationRole B ON A.ID = B.OrganizationId
                                        LEFT OUTER JOIN Role C ON B.RoleId = C.RoleId
                                        ORDER BY OrganizationId
                                    END
                                SELECT  V1.OrganizationId, V1.OrganizationName, V1.ShortDescription , LEFT(D.ListRole, LEN(D.ListRole) - 1) AS ListRole
                                FROM tmpOrganizationRole V1
                                CROSS APPLY
                                (
                                    SELECT RoleName + ', ' AS [text()]
                                    FROM tmpOrganizationRole V2
                                    WHERE V1.OrganizationId = V2.OrganizationId	
                                    ORDER BY OrganizationId
                                    FOR XML PATH('')
                                ) D (ListRole)
                                WHERE V1.ServiceId = @ServiceId
                                GROUP BY OrganizationId, OrganizationName, ListRole, ShortDescription
                                ORDER BY OrganizationId
                                ";

        public const string UpdateOrganizationRole = @"
                                IF EXISTS (SELECT * FROM OrganizationRole WHERE OrganizationId = @OrganizationId AND RoleId = @RoleId)
	                                BEGIN
		                                DELETE FROM OrganizationRole 
                                        WHERE OrganizationId = @OrganizationId AND RoleId = @RoleId	
		                                INSERT INTO OrganizationRole(OrganizationId, RoleId) 
                                        VALUES (@OrganizationId, @RoleId)
	                                END
                                ELSE
                                    BEGIN
	                                    INSERT INTO OrganizationRole(OrganizationId, RoleId) 
                                        VALUES (@OrganizationId, @RoleId)
                                    END
                                ";
        public const string DeleteAllOrganizationRole = @"
                            DELETE FROM OrganizationRole
                            WHERE OrganizationId = @OrganizationId
                            ";
        #endregion


        #region TrustRegion

        public const string ListTrustRegion = @"";

        public const string AddTrustRegion = @"";
        #endregion

        #region Facility

        public const string ListAllFacility = @"";
        public const string FacilityDetails = @"";
        public const string AddFacility = @"";
        public const string UpdateFacility = @"";
        public const string ChangeFacilityStatus = @"";
        #endregion
        #endregion

        #region LacHV

        #region Program
        public const string SqlRetrieveProgram = @"
            SELECT 
                P.ProgramId,
                ProgramName,
                Description,
                P.IsActive,
                C.ContactId,
                C.FirstName+' '+C.SurName AS ContactName,
                Org.Id AS OrganizationId
            FROM Program P
            INNER JOIN Organizations Org
                ON P.OrganizationId = Org.Id
            INNER JOIN Contact C
                ON P.ContactId = C.ContactId  
            WHERE P.ProgramId = @ProgramId 
        ";

        public const string SqlRetrieveAllProgramDataTable = @"
            SELECT 
                P.ProgramId,
                ProgramName,
                Description,
                P.IsActive,
                C.ContactId,
                C.FirstName,
                C.SurName,
                Org.ID AS OrganizationId
            FROM Program P
            INNER JOIN Organizations Org
                ON P.OrganizationId = Org.Id
            INNER JOIN Contact C
                ON P.ContactId = C.ContactId
            ORDER BY P.ProgramId DESC
        ";
        public const string SqlInsertProgram = @"
            INSERT INTO Program(
                ProgramName,
                Description,
                IsActive,
                ContactId,
                OrganizationId)
            VALUES(
                @ProgramName,
                @Description,
                @IsActive,
                @ContactId,
                @OrganizationId)
        ";

        public const string SqlUpdateProgram = @"
            UPDATE Program
            SET
                ProgramName = @ProgramName,
                Description = @Description,
                ContactId = @ContactId,
                OrganizationId = @OrganizationId
            WHERE
                ProgramId = @ProgramId
        ";


        #endregion

        #region Team
        public const string SqlRetrieveAllTeams = @"
            SELECT 
                T.TeamName,
                T.ContactId,
                T.DepartmentId,
                T.AddressLine1,
                T.Postcode,
                T.IsActive,
                T.Date,
                T.TeamId,
                C.SurName,
                C.FirstName
            FROM Team T
            INNER JOIN Contact C
                ON T.ContactId = C.ContactId 
            ORDER BY TeamId DESC
        ";

        public const string SqlRetrieveTeam = @"
            SELECT 
                T.TeamName,
                T.ShortDescription,
                T.FullDescription,
                T.DepartmentId,
                T.ContactId,
                T.TypeOfBusinessId,
                Org.ID AS OrganizationId,
                T.AddressLine1,
                T.AddressLine2,
                T.AddressLine3,
                T.Postcode,
                T.Town,
                T.County,
                T.CountryId,
                T.Phone,
                T.Fax,
                T.Email,
                T.WebAdress,
                T.IsActive,
                T.Date,
                T.TeamId,
                (C.SurName + ' ' + C.FirstName) AS ContactName,
                Type.TypeName,
                Type.SicCode                
            FROM 
                Team T
            LEFT JOIN Contact C
                ON T.ContactId = C.ContactId
            INNER JOIN Department D
                ON T.DepartmentId = D.DepartmentId
            INNER JOIN Directorate Dir
                ON D.DirectorateId = Dir.DirectorateId
            INNER JOIN Organizations Org
                ON Dir.OrganizationId = Org.ID
            LEFT JOIN TypeOfBusiness Type
                ON T.TypeOfBusinessId = Type.ID
            WHERE
                TeamId = @TeamId
        ";

        public const string SqlRetrieveTeamsByDepartment = @"
            SELECT 
                T.TeamName,
                T.ContactId,
                T.DepartmentId,
                T.AddressLine1,
                T.Postcode,
                CASE(T.[IsActive]) WHEN 1 THEN 'Yes' ELSE 'No' END As IsActive,
                T.Date,
                T.TeamId,
                C.SurName,
                C.FirstName
            FROM Team T
            LEFT JOIN Contact C
                ON T.ContactId = C.ContactId 
            WHERE
                T.{0} = @ConditionField 
            ORDER BY TeamId DESC
        ";

        public const string SqlRetrieveOrganizationIdByDepartment = @"
            SELECT 
                Org.ID AS OrganizationId,Org.Name,Org.ShortDescription,Org.ContactID,Org.Address1, 
                     Org.Address2,Org.Address3,Org.City,Org.County,Org.Country, 
                     Org.PreferredOrg,Org.ExpOfInterest,Org.TypeOfBusinessId,Org.FullDescription, 
                     Org.PhoneNumber,Org.Fax,Org.Email,Org.WebAddr,Org.CharityNumber, 
                     Org.CompanyNumber,Org.GovOffRegionID,Org.TrustRegionID, 
                     Org.TrustDistrictID,Org.PostCode,Org.IsActive,Org.ServiceId
            FROM 
				Department Dep
			INNER JOIN Directorate Der
				ON Dep.DirectorateId = Der.DirectorateId
			INNER JOIN Organizations Org
				ON Der.OrganizationId = Org.ID
            WHERE
                DepartmentId = @DepartmentId 
        ";

        public const string SqlInsertTeam = @"
            INSERT INTO Team(
                TeamName,
                ShortDescription,
                FullDescription,
                DepartmentId,
                ContactId,
                TypeOfBusinessId,
                AddressLine1,
                AddressLine2,
                AddressLine3,
                Postcode,
                Town,
                County,
                CountryId,
                Phone,
                Fax,
                Email,
                WebAdress,
                IsActive,
                Date            
            )
            VALUES(
                @TeamName,
                @ShortDescription,
                @FullDescription,
                @DepartmentId,
                @ContactId,
                @TypeOfBusinessId,
                @AddressLine1,
                @AddressLine2,
                @AddressLine3,
                @Postcode,
                @Town,
                @County,
                @CountryId,
                @Phone,
                @Fax,
                @Email,
                @WebAdress,
                @IsActive,
                @Date
            )
        ";

        public const string SqlUpdateTeam = @"
            UPDATE Team
            SET 
                TeamName = @TeamName,
                ShortDescription = @ShortDescription,
                FullDescription = @FullDescription,
                DepartmentId = @DepartmentId,
                ContactId = @ContactId,
                TypeOfBusinessId = @TypeOfBusinessId,
                AddressLine1 = @AddressLine1,
                AddressLine2 = @AddressLine2,
                AddressLine3 = @AddressLine3,
                Postcode = @Postcode,
                Town = @Town,
                County = @County,
                CountryId = @CountryId,
                Phone = @Phone,
                Fax = @Fax,
                Email = @Email,
                WebAdress = @WebAdress,
                IsActive = @IsActive,
                Date = @Date
            WHERE
                TeamId = @TeamId
        ";

        #endregion

        #region TrustDistrict

        public const string SqlSelectAll = @"
            SELECT 
                TrustDistrictId,
                TrustDistrictName,
                Description,
                TrustRegionId,
                IsActive
            FROM 
                TrustDistrict
        ";
        public const string SqlRetrieveTrustDistrictsByRegion = @"
            SELECT 
                D.TrustDistrictId,
                D.TrustDistrictName,
                D.Description,
                D.TrustRegionId,
                R.TrustRegionName,
                CASE(D.IsActive) WHEN 1 THEN 'Yes' ELSE 'No' END As IsActive
            FROM 
                TrustDistrict D
            INNER JOIN TrustRegion R
                ON D.TrustRegionId = R.TrustRegionId
            WHERE 
                D.{0} = @ConditionField
            ORDER BY D.TrustDistrictId DESC
        ";
        public const string SqlRetrieveTrustDistrict = @"
            SELECT 
                D.TrustDistrictId,
                D.TrustDistrictName,
                D.Description,
                D.IsActive,
                R.TrustRegionId,
                R.TrustRegionName
            FROM 
                TrustDistrict D
            INNER JOIN TrustRegion R
                ON D.TrustRegionId = R.TrustRegionId
            WHERE 
                TrustDistrictId = @TrustDistrictId
        ";
        public const string SqlInsertTrustDistrict = @"
            INSERT INTO TrustDistrict(
                TrustDistrictName,
                Description,
                TrustRegionId)
            VALUES(
                @TrustDistrictName,
                @Description,
                @TrustRegionId)
        ";
        public const string SqlUpdateTrustDistrict = @"
            UPDATE TrustDistrict
            SET 
                TrustDistrictName = @TrustDistrictName,
                Description =@Description,
                TrustRegionId =@TrustRegionId
            WHERE 
                TrustDistrictId = @TrustDistrictId
        ";

        #endregion

        #endregion

        #region TIENVN

        #region Goverment Office Region
        public const string SqlGetAllGovOfficeRegion =
            @"SELECT GovOfficeRegionId,GovOfficeRegionName,Description,GovOfficeRegion.CountyId,CountyName,IsActivated
                FROM GovOfficeRegion,County where GovOfficeRegion.CountyId = County.CountyId";

        public const string SqlGetGovOfficeRegionById =
            @"SELECT GovOfficeRegionId,GovOfficeRegionName,Description,GovOfficeRegion.CountyId,CountyName,IsActivated
                FROM GovOfficeRegion,County where GovOfficeRegion.CountyId = County.CountyId and GovOfficeRegionId=@GovOfficeRegionId";
        #endregion

        #region Department
        public const string SqlAddDepartment =
            @"INSERT INTO Department(
                DirectorateId,
                DepartmentName,
                ShortDescription,
                ContactId,
                AddressLine1,
                AddressLine2,
                AddressLine3,
                Postcode,
                Town,
                County,
                Country,
                TypeOfBusinessId,
                SicCode,
                FullDescription,
                PhoneNumber,
                Fax,
                Email,
                WebAddress,
                IsActivated)
            VALUES(
                @DirectorateId,
                @DepartmentName,
                @ShortDescription,
                @ContactId,
                @AddressLine1,
                @AddressLine2,
                @AddressLine3,
                @Postcode,
                @Town,
                @County,
                @Country,
                @TypeOfBusinessId,
                @SicCode,
                @FullDescription,
                @PhoneNumber,
                @Fax,
                @Email,
                @WebAddress,
                @IsActivated)";

        public const string SqlUpdateDepartment =
            @"UPDATE Department 
                SET 
                    DepartmentName = @DepartmentName,
                    ShortDescription = @ShortDescription,
                    ContactId = @ContactId,
                    AddressLine1 = @AddressLine1,
                    AddressLine2 = @AddressLine2,
                    AddressLine3 = @AddressLine3,
                    Postcode = @Postcode,
                    Town = @Town,
                    County = @County,
                    Country = @Country,
                    TypeOfBusinessId = @TypeOfBusinessId,
                    SicCode = @SicCode,
                    FullDescription = @FullDescription,
                    PhoneNumber = @PhoneNumber,
                    Fax = @Fax,
                    Email = @Email,
                    WebAddress = @WebAddress
                WHERE DepartmentId = @DepartmentId";

        public const string SqlGetAllDepartmentByDirectorateId =
            @"SELECT
                DirectorateId,
                DepartmentId,
                DepartmentName,
                ShortDescription,
                Department.ContactId,
				(Contact.FirstName + ' ' + Contact.SurName) as ContactName,
                AddressLine1,
                AddressLine2,
                AddressLine3,
                Postcode,
                Town,
                County,
                Country,
                TypeOfBusinessId,
                SicCode,
                FullDescription,
                PhoneNumber,
                Fax,
                Department.Email,
                WebAddress,
                Case(IsActivated) when 1 then 'Yes' else 'No' END AS IsActivated
            FROM Department,Contact
            WHERE 
                DirectorateId = @DirectorateId AND
                Department.ContactId = Contact.ContactId
            ORDER BY DepartmentID DESC";

        public const string SqlGetDepartmentById =
            @"SELECT
                DirectorateId,
                DepartmentId,
                DepartmentName,
                ShortDescription,
                Department.ContactId,
				(Contact.FirstName + ' ' + Contact.SurName) as ContactName,
                AddressLine1,
                AddressLine2,
                AddressLine3,
                Postcode,
                Town,
                County,
                Country,
                TypeOfBusinessId,
                TypeOfBusiness.TypeName as TypeOfBusinessName,
                Department.SicCode,
                FullDescription,
                PhoneNumber,
                Fax,
                Department.Email,
                WebAddress,
                IsActivated
            FROM Department,Contact,TypeOfBusiness
            WHERE DepartmentId = @DepartmentId AND Department.ContactId = Contact.ContactId AND TypeOfBusiness.Id = TypeOfBusinessId";

        public const string SqlMarkActivated =
            @"UPDATE Department SET IsActivated = @IsActivated WHERE DepartmentId = @DepartmentId";

        public const string SqlCheckDepartmentNameExists =
            @"SELECT COUNT(DepartmentName) FROM Department WHERE DepartmentName = @DepartmentName";
        #endregion

        #region Service Premise
        public const string SqlSelectServicePremiseByServiceId =
            @"SELECT PremiseId,PremiseName,Address,PhoneNumber,ProjectCode,IsActivated,ServiceId
                FROM Service_Premise
                WHERE ServiceId = @ServiceId
                AND IsActivated = 1";

        public const string SqlSelectServicePremiseNotBelongService =
            @"SELECT PremiseId,PremiseName,Address,PhoneNumber,ProjectCode,IsActivated,ServiceId
                FROM Service_Premise
                WHERE ServiceId is null
                AND IsActivated = 1";

        public const string SqlUpdatePremiseAddToService =
            @"UPDATE Service_Premise
                SET ProjectCode = @ProjectCode,
                    ServiceId = @ServiceId
                WHERE PremiseId = @PremiseId";
        #endregion

        #region Premise
        public const string SqlAddPremise =
            @"INSERT INTO Premises(
                LocationName,
                KnownAs,
                PrimaryLocation,
                LocationManaged,
                OrgnisationId,
                LocationStatus,
                StNetworkConectivity,
                LocationStatusDate,
                LocationType,
                LocationDescription,
                AddressLine1,
                AddressLine2,
                AddressLine3,
                PhoneNumber,
                Postcode,
                FaxNumber,
                CityTown,
                MinicommNumber,
                County,
                IsNewShop,
                CountryId,
                ShopFlagDate,
                SpecialistShop,
                Accreditations,
                JcpOffices,
                MediaContact,
                CateringContact,
                CateringType,
                LocalDemographicIssue,
                IsNetwork,
                ClientItFacilityDetails,
                LocalDemographicNotes,
                RoomAvailability,
                VolunteeringOpportunities,
                OutreachLocation,
                LocalHotel,
                TravelDetails,
                VisitorParkingOnsite,
                TravelNearestBus,
                VisitorParkingSpaces,
                TravelNearestRail,
                TravelNearestAirport,
                VisitorParkingAlternative,
                HostVisits,
                HostingContact,
                RoomOnlyRate,
                NegotiatedRoomOnlyRate,
                BbRate,
                BbNegotiatedRate,
                DdRate,
                DdNegotiatedRate,
                RateDay,
                NegotiatedRateDay,
                TeaAndCoffee,
                Lunch,
                LastNegotiatedDate,
                ReNegotiatedOn,
                NoOfMeetingRoom,
                PreferedStatus,
                MeetingRoomRatePerDay,
                Codings,
                Comments,
                IsActive)
            VALUES(
                @LocationName,
                @KnownAs,
                @PrimaryLocation,
                @LocationManaged,
                @OrgnisationId,
                @LocationStatus,
                @StNetworkConectivity,
                @LocationStatusDate,
                @LocationType,
                @LocationDescription,
                @AddressLine1,
                @AddressLine2,
                @AddressLine3,
                @PhoneNumber,
                @Postcode,
                @FaxNumber,
                @CityTown,
                @MinicommNumber,
                @County,
                @IsNewShop,
                @CountryId,
                @ShopFlagDate,
                @SpecialistShop,
                @Accreditations,
                @JcpOffices,
                @MediaContact,
                @CateringContact,
                @CateringType,
                @LocalDemographicIssue,
                @IsNetwork,
                @ClientItFacilityDetails,
                @LocalDemographicNotes,
                @RoomAvailability,
                @VolunteeringOpportunities,
                @OutreachLocation,
                @LocalHotel,
                @TravelDetails,
                @VisitorParkingOnsite,
                @TravelNearestBus,
                @VisitorParkingSpaces,
                @TravelNearestRail,
                @TravelNearestAirport,
                @VisitorParkingAlternative,
                @HostVisits,
                @HostingContact,
                @RoomOnlyRate,
                @NegotiatedRoomOnlyRate,
                @BbRate,
                @BbNegotiatedRate,
                @DdRate,
                @DdNegotiatedRate,
                @RateDay,
                @NegotiatedRateDay,
                @TeaAndCoffee,
                @Lunch,
                @LastNegotiatedDate,
                @ReNegotiatedOn,
                @NoOfMeetingRoom,
                @PreferedStatus,
                @MeetingRoomRatePerDay,
                @Codings,
                @Comments,
                @IsActive)
            ";

        public const string SqlUpdatePremise =
            @"UPDATE Premises SET
                LocationName = @LocationName,
                KnownAs = @KnownAs,
                PrimaryLocation = @PrimaryLocation,
                LocationManaged = @LocationManaged,
                OrgnisationId = @OrgnisationId,
                LocationStatus = @LocationStatus,
                StNetworkConectivity = @StNetworkConectivity,
                LocationStatusDate = @LocationStatusDate,
                LocationType = @LocationType,
                LocationDescription = @LocationDescription,
                AddressLine1 = @AddressLine1,
                AddressLine2 = @AddressLine2,
                AddressLine3 = @AddressLine3,
                PhoneNumber = @PhoneNumber,
                Postcode = @Postcode,
                FaxNumber = @FaxNumber,
                CityTown = @CityTown,
                MinicommNumber = @MinicommNumber,
                County = @County,
                IsNewShop = @IsNewShop,
                CountryId = @CountryId,
                ShopFlagDate = @ShopFlagDate,
                SpecialistShop = @SpecialistShop,
                Accreditations = @Accreditations,
                JcpOffices = @JcpOffices,
                MediaContact = @MediaContact,
                CateringContact = @CateringContact,
                CateringType = @CateringType,
                LocalDemographicIssue = @,
                IsNetwork = @IsNetwork,
                ClientItFacilityDetails = @ClientItFacilityDetails,
                LocalDemographicNotes = @LocalDemographicNotes,
                RoomAvailability = @RoomAvailability,
                VolunteeringOpportunities = @VolunteeringOpportunities,
                OutreachLocation = @OutreachLocation,
                LocalHotel = @LocalHotel,
                TravelDetails = @TravelDetails,
                VisitorParkingOnsite = @VisitorParkingOnsite,
                TravelNearestBus = @TravelNearestBus,
                VisitorParkingSpaces = @VisitorParkingSpaces,
                TravelNearestRail = @TravelNearestRail,
                TravelNearestAirport = @TravelNearestAirport,
                VisitorParkingAlternative = @VisitorParkingAlternative,
                HostVisits = @HostVisits,
                HostingContact = @HostingContact,
                RoomOnlyRate = @RoomOnlyRate,
                NegotiatedRoomOnlyRate = @NegotiatedRoomOnlyRate,
                BbRate = @BbRate,
                BbNegotiatedRate = @BbNegotiatedRate,
                DdRate = @DdRate,
                DdNegotiatedRate = @DdNegotiatedRate,
                RateDay = @RateDay,
                NegotiatedRateDay = @NegotiatedRateDay,
                TeaAndCoffee = @TeaAndCoffee,
                Lunch = @Lunch,
                LastNegotiatedDate = @LastNegotiatedDate,
                ReNegotiatedOn = @ReNegotiatedOn,
                NoOfMeetingRoom = @NoOfMeetingRoom,
                PreferedStatus = @PreferedStatus,
                MeetingRoomRatePerDay = @MeetingRoomRatePerDay,
                Codings = @Codings,
                Comments = @Comments,
                IsActive = @IsActive
            WHERE PremiseId = @PremiseId
            ";

        public const string SqlGetAllPremise =
            @"SELECT
                PremiesId,
                LocationName,
                KnownAs,
                PrimaryLocation,
                LocationManaged,
                OrgnisationId,
                LocationStatus,
                StNetworkConectivity,
                convert(varchar(10),LocationStatusDate,101) as LocationStatusDate,
                LocationType,
                LocationDescription,
                AddressLine1,
                AddressLine2,
                AddressLine3,
                PhoneNumber,
                Postcode,
                FaxNumber,
                CityTown,
                MinicommNumber,
                County,
                IsNewShop,
                CountryId,
                convert(varchar(10),ShopFlagDate,101) as ShopFlagDate,
                SpecialistShop,
                Accreditations,
                JcpOffices,
                MediaContact,
                CateringContact,
                CateringType,
                LocalDemographicIssue,
                IsNetwork,
                ClientItFacilityDetails,
                LocalDemographicNotes,
                RoomAvailability,
                VolunteeringOpportunities,
                OutreachLocation,
                LocalHotel,
                TravelDetails,
                VisitorParkingOnsite,
                TravelNearestBus,
                VisitorParkingSpaces,
                TravelNearestRail,
                TravelNearestAirport,
                VisitorParkingAlternative,
                HostVisits,
                HostingContact,
                RoomOnlyRate,
                NegotiatedRoomOnlyRate,
                BbRate,
                BbNegotiatedRate,
                DdRate,
                DdNegotiatedRate,
                RateDay,
                NegotiatedRateDay,
                TeaAndCoffee,
                Lunch,
                convert(varchar(10),LastNegotiatedDate,101) as LastNegotiatedDate,
                convert(varchar(10),ReNegotiatedOn,101) as ReNegotiatedOn,
                NoOfMeetingRoom,
                PreferedStatus,
                MeetingRoomRatePerDay,
                Codings,
                Comments,
                case IsActive when 1 then 'Yes' else 'No' end as IsActive
            FROM Premise
            ";

        public const string SqlGetPremiseById =
            @"SELECT
                PremiseId,
                LocationName,
                KnownAs,
                PrimaryLocation,
                LocationManaged,
                OrgnisationId,
                LocationStatus,
                StNetworkConectivity,
                convert(varchar(10),LocationStatusDate,101) as LocationStatusDate,
                LocationType,
                LocationDescription,
                AddressLine1,
                AddressLine2,
                AddressLine3,
                PhoneNumber,
                Postcode,
                FaxNumber,
                CityTown,
                MinicommNumber,
                County,
                IsNewShop,
                CountryId,
                convert(varchar(10),ShopFlagDate,101) as ShopFlagDate,
                SpecialistShop,
                Accreditations,
                JcpOffices,
                MediaContact,
                CateringContact,
                CateringType,
                LocalDemographicIssue,
                IsNetwork,
                ClientItFacilityDetails,
                LocalDemographicNotes,
                RoomAvailability,
                VolunteeringOpportunities,
                OutreachLocation,
                LocalHotel,
                TravelDetails,
                VisitorParkingOnsite,
                TravelNearestBus,
                VisitorParkingSpaces,
                TravelNearestRail,
                TravelNearestAirport,
                VisitorParkingAlternative,
                HostVisits,
                HostingContact,
                RoomOnlyRate,
                NegotiatedRoomOnlyRate,
                BbRate,
                BbNegotiatedRate,
                DdRate,
                DdNegotiatedRate,
                RateDay,
                NegotiatedRateDay,
                TeaAndCoffee,
                Lunch,
                convert(varchar(10),LastNegotiatedDate,101) as LastNegotiatedDate,
                convert(varchar(10),ReNegotiatedOn,101) as ReNegotiatedOn,
                NoOfMeetingRoom,
                PreferedStatus,
                MeetingRoomRatePerDay,
                Codings,
                Comments,
                IsActive
            FROM Premise
            WHERE PremiseId = @PremiseId
            ";

        public const string SqlGetPremiseByLocationType =
            @"SELECT
                PremiseId,
                LocationName,
            FROM Premise 
            WHERE LocationType = @LocationType
            ";

        public const string SqlCheckNameExists =
            @"SELECT TOP 1 PremiseId
            FROM Premise 
            WHERE LocationName = @LocationName
            ";
        #endregion
        #endregion
    }
}